/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Windlord
 */
public class SQLParser {

    private String CurrentSQLString;
    /**
     * @return the joinTable
     */
    public static List<String> getJoinTable() {
        return joinTable;
    }

    /**
     * @param aJoinTable the joinTable to set
     */
    public static void setJoinTable(List<String> aJoinTable) {
        joinTable = aJoinTable;
    }
    private List<ParseColumn> ListColumn;
    static List<ParseTable> ListTable;
    private static String database_name;

    /**
     * @return the ListColumn
     */
    public List<ParseColumn> getListColumn() {
        return ListColumn;
    }

    /**
     * @param aListColumn the ListColumn to set
     */
    public void setListColumn(List<ParseColumn> aListColumn) {
        ListColumn = aListColumn;
    }
    public String GetDatabaseName()
    {
        return database_name;
    }
    private static List<String> joinTable;
    public List<String> GetListJoinTable()
    {
        return getJoinTable();
    }
    private static List<String> list_tableName;
    public List<String> GetListTableName()
    {
        return list_tableName;
    }
    private static List<String> fieldName;//ten cac cot
    public List<String> GetFieldName()
    {
        return fieldName;
    }
    private static List<String> ListUpdateValue;
    public List<String> GetListUpdateValue()
    {
        return ListUpdateValue;
    }
    //private static List<String> conditions;//cac dieu kien a>=b
    private static List<String> values;//cac gia tri
     public List<String> GetListValue()
    {
        return values;
    }
    //private static List<String> andorlist;
    private static WhereCondition sWhereCondition;
    public WhereCondition GetWhereCondition()
    {
        return sWhereCondition;
    }
    private static WhereCondition sJoinCondition;
    public WhereCondition GetJoinCondition()
    {
        return sJoinCondition;
    }
    public SQLParser()
    {
            sWhereCondition=new WhereCondition();
            sJoinCondition=new WhereCondition();
            fieldName=new ArrayList<String>();
            values=new ArrayList<String>();
            ListUpdateValue=new ArrayList<String>();
            list_tableName=new ArrayList<String>();
            joinTable=new ArrayList<String>();
            
            //////////////////
            ListColumn=new ArrayList<ParseColumn>();
            ListTable=new ArrayList<ParseTable>();
            //////////////////
            
    }
   /* public static void main(String[] args) 
	{ 
            //////////////////
            ListColumn=new ArrayList<ParseColumn>();
            ListTable=new ArrayList<ParseTable>();
            //////////////////
            
            //conditions=new ArrayList<String>();
            sWhereCondition=new WhereCondition();
            sJoinCondition=new WhereCondition();
            fieldName=new ArrayList<String>();
            values=new ArrayList<String>();
            ListUpdateValue=new ArrayList<String>();
            //andorlist=new ArrayList<String>();
            list_tableName=new ArrayList<String>();
            joinTable=new ArrayList<String>();
            String sc="using database select k.a,k.b from   king k, nhanvien n where k.a=n.d";
            String ss="insert into table (v,n m,m) values (a,b,c)";
            String sd="delete from k where k=n and n=m";
            String su="update k set k=n , n=m";
            System.out.println(sc);
            if (CheckSelectSyntax(sc))
            {
                for (int i=0;i<list_tableName.size();i++)
                    System.out.println("table "+list_tableName.get(i));
                for (int i=0;i<fieldName.size();i++)
                {
                    System.out.println(fieldName.get(i));
                }
            }
	}*/
    public void ResetData()
    {
        getJoinTable().clear();
        database_name="";
        list_tableName.clear();
        fieldName.clear();
        sWhereCondition.Clear();
        values.clear();
        sJoinCondition.Clear();
        ListUpdateValue.clear();
        CurrentSQLString="";
        getListColumn().clear();
        ListTable.clear();
    }
    public boolean CheckSelectSyntax(String str)
    {
        ResetData();
        String s=str.toLowerCase();
        s=s.trim();
        s=s.replaceAll("\n", " ");
        s=s.replaceAll("\\s+", " ");
        s=s.replaceAll(", ", ",");
        s=s.replaceAll(" ,", ",");
        if (s.startsWith("using "))
            database_name=s.substring(s.indexOf("using ")+6,s.indexOf(" select"));
        s=s.substring(s.indexOf(" select")+1);
/*        if (database_name==""||!IsRegular(database_name))
            return false;*/
        if (!s.startsWith("select "))
            return false;
        if (!s.contains(" from "))
            return false;
        String dataField=(String) s.subSequence(s.indexOf("select ")+7,s.indexOf(" from"));
        fieldName=GetItem(dataField);
        if (s.contains(" where"))
            if (!s.contains(" where "))            
                return false;
            else
            {
                String temp=(String) s.subSequence(s.indexOf(" where ")+7, s.length());
                sWhereCondition.SetConditionList(GetCondition(temp));
                sWhereCondition.SetAndOrList(GetAndOrList(temp));
                if (!CheckCondition(sWhereCondition.GetConditionList()))
                    return false;
                //list_tableName=GetItem((String)s.subSequence(s.indexOf("from ")+5, s.indexOf(" where")));
                list_tableName=GetTableName((String)s.subSequence(s.indexOf("from ")+5, s.indexOf(" where")));
             }
        else
            list_tableName=GetTableName((String)s.subSequence(s.indexOf("from ")+5, s.length()));
        if (list_tableName==null||list_tableName.size()<1)
            return false;
        
        ListTable=GetParseTable();
        ListColumn=GetParseColumn(fieldName);
        DinhDangDieuKien();
        return true;
    }
    public static List<String> GetTableName(String str)
    {
        List<String> kq=new ArrayList<String>();
        if (str.contains(" join "))
            if (!str.contains(" on "))
                return null;
            else
            {
                kq=GetItem(str.substring(0,str.indexOf(" join")));
                setJoinTable(GetItem(str.substring(str.indexOf(" join ")+6,str.indexOf(" on "))));
                if (getJoinTable().size()<1)
                    return null;
                sJoinCondition.SetConditionList(GetCondition(str.substring(str.indexOf(" on ")+4)));
                sJoinCondition.SetAndOrList(GetAndOrList(str.substring(str.indexOf(" on ")+4)));
            }
        else 
            kq=GetItem(str);
        return kq;
    }

    public static List<String> GetAndOrList(String str)
    {
        List<String> kq=new ArrayList<String>();
        boolean dk=true;
        while (dk)
        {
            if (str.contains(" and "))
            {
                kq.add("and");
                str=str.replace(" and ", " ");
            }
            else 
                if (str.contains(" or "))
                {
                    kq.add("or");
                    str=str.replace(" or ", " ");
                }
                else dk=false;
        }
        return kq;
    }
    public static List<String> GetItem(String str)
    {
        String[] temp=str.split(",");
        List<String> kq=new ArrayList<String>();
        for (int i=0;i<temp.length;i++)
            kq.add(temp[i]);
        return kq;
    }
    public static List<String> GetCondition(String str)
    {
       List<String> kq=new ArrayList<String>();
       String[] temp=str.split(" and ");
       for (int i=0;i<temp.length;i++)
       {
           String[] temp2=temp[i].split(" or ");
           for (int j=0;j<temp2.length;j++)
               kq.add(temp2[j]);
       }
       return kq;
    }
    public static boolean CheckCondition(List<String> conditionList)
    {
        for (int i=0;i<conditionList.size();i++)
        {
            String operator="=";
            if (conditionList.get(i).contains(">"))
                operator=">";
            if (conditionList.get(i).contains("<"))
                operator="<";
            if (conditionList.get(i).contains("="))
                operator="=";
            if (conditionList.get(i).contains(">="))
                operator=">=";
            if (conditionList.get(i).contains("<="))
                operator="<=";
            if (conditionList.get(i).contains("!="))
                operator="!=";
            if (!CheckString(conditionList.get(i),operator))
                return false;
        }
        return true;
    }
    public static boolean CheckString(String str, String operator)
    {
        String[] temp=str.split(operator);
        if (temp.length!=2)
            return false;
        for (int i=0;i<temp.length;i++)
        {
            if (!IsRegular(temp[i]))
                return false;
        }
        return true;
    }
    public boolean CheckInsertSyntax(String str)
    {
        ResetData();
        String s=str.toLowerCase();
        s=s.trim();
        s=s.replaceAll("\n", " ");
        s=s.replaceAll("\\s+", " ");
        s=s.replaceAll(", ", ",");
        s=s.replaceAll(" ,", ",");
        if (s.startsWith("using "))
            database_name=s.substring(s.indexOf("using ")+6,s.indexOf(" insert"));
        s=s.substring(s.indexOf(" insert")+1);
      /*  if (database_name==""||!IsRegular(database_name))
            return false;
/*        s=s.replaceAll("( ", "(");
        s=s.replaceAll(" )", ")");*/
        if (!s.startsWith("insert into "))
            return false;
        if (!s.contains(" values "))
            return false;
        String insert_values=s.substring(s.indexOf("into ")+5,s.indexOf(" values"));
        
        if (insert_values.contains("(")&& insert_values.contains(")"))
        {
            if (insert_values.indexOf("(")>insert_values.indexOf(")"))
                return false;
            list_tableName.add(insert_values.substring(0,insert_values.indexOf(" (")));
            insert_values=insert_values.substring(insert_values.indexOf("(")+1,insert_values.indexOf(")"));
            insert_values=insert_values.replaceAll(" ","");
            fieldName=GetItem(insert_values);
        }
        else 
            list_tableName.add(insert_values);
        if (list_tableName.get(0)=="" || list_tableName.get(0).contains(" "))
            return false;
        String afterValues=s.substring(s.indexOf("values ")+7);
        afterValues=afterValues.replaceAll(" ", "");
        if (!afterValues.contains("(")||!afterValues.contains(")")||afterValues.indexOf("(")>afterValues.indexOf(")"))
            return false;
        afterValues=afterValues.substring(1,afterValues.length()-1);
        values=GetItem(afterValues);
        for (int i=0;i<values.size();i++)
            if (!IsRegular(values.get(i)))
                return false;
        for (int i=0;i<fieldName.size();i++)
            if (!IsRegular(fieldName.get(i)))
                return false;
        if (fieldName.size()>0 && fieldName.size()!=values.size())
            return false;
        return true;
    }
    public static boolean IsCharacter(char c)
    {
        if (!((c >='A' && c<='Z')||(c>='a'&&c<='z')||(c>='0'&&c<='9')||c=='_'||c=='\''||c=='.'||c==' '||c=='*'))
           return false;
        return true;
    }
    public static boolean IsRegular(String str)
    {
        for (int i=0;i<str.length();i++)
            if (!IsCharacter(str.charAt(i)))
                return false;
        return true;
    }
    public boolean CheckDeleteSyntax(String str)
    {
        ResetData();
        String s=str.toLowerCase();
        s=s.trim();
        s=s.replaceAll("\n", " ");
        s=s.replaceAll("\\s+", " ");
        s=s.replaceAll(", ", ",");
        s=s.replaceAll(" ,", ",");
        if (s.startsWith("using "))
            database_name=s.substring(s.indexOf("using ")+6,s.indexOf(" delete"));
        s=s.substring(s.indexOf(" delete")+1);
/*        if (database_name==""||!IsRegular(database_name))
            return false;*/
        if (!s.startsWith("delete from "))
            return false;
        if (s.contains(" where"))
            if (!s.contains(" where "))            
                return false;
            else
            {
                String temp=(String) s.subSequence(s.indexOf(" where ")+7, s.length());
                sWhereCondition.SetConditionList(GetCondition(temp));
                sWhereCondition.SetAndOrList(GetAndOrList(temp));
                if (!CheckCondition(sWhereCondition.GetConditionList()))
                    return false;
                //list_tableName=GetItem((String)s.subSequence(s.indexOf("from ")+5, s.indexOf(" where")));
                list_tableName=GetItem((String)s.subSequence(s.indexOf("from ")+5, s.indexOf(" where")));
             }
        else
            list_tableName=GetTableName((String)s.subSequence(s.indexOf("from ")+5, s.length()));
        if (list_tableName.size()!=1)
            return false;
        if (!IsRegular(list_tableName.get(0)))
            return false;
        ListTable=GetParseTable();
        ListColumn=GetParseColumn(fieldName);
        DinhDangDieuKien();
        return true;
    }
    public boolean CheckUpdateSyntax(String str)
    {
        ResetData();
        String s=str.toLowerCase();
        s=s.trim();
        s=s.replaceAll("\n", " ");
        s=s.replaceAll("\\s+", " ");
        s=s.replaceAll(", ", ",");
        s=s.replaceAll(" ,", ",");
        if (s.startsWith("using "))
            database_name=s.substring(s.indexOf("using ")+6,s.indexOf(" update"));
        s=s.substring(s.indexOf(" update")+1);
/*        if (database_name==""||!IsRegular(database_name))
            return false;*/
        if (!s.startsWith("update "))
            return false;
        if (!s.contains(" set "))
            return false;
        list_tableName=GetTableName((String)s.subSequence(s.indexOf("update ")+7, s.indexOf(" set")));
        if (list_tableName.size()!=1)
            return false;
        if (!IsRegular(list_tableName.get(0)))
            return false;
        if (s.contains(" where"))
            if (!s.contains(" where "))            
                return false;
            else
            {
                String temp=(String) s.subSequence(s.indexOf("set ")+4, s.indexOf(" where "));
                ListUpdateValue=GetListUpdateValue(temp);
                sWhereCondition.SetConditionList(GetCondition(s.substring(s.indexOf(" where ")+7)));
                sWhereCondition.SetAndOrList(GetAndOrList(s.substring(s.indexOf(" where ")+7)));
                if (!CheckCondition(sWhereCondition.GetConditionList()))
                    return false;
             }
        else
        {
            ListUpdateValue=GetListUpdateValue(s.substring(s.indexOf("set ")+4));
        }
        if (ListUpdateValue.size()==0)
            return false;
        if (!CheckUpdateValue())
            return false;
        ListTable=GetParseTable();
        ListColumn=GetParseColumn(fieldName);
        DinhDangDieuKien();
        return true;
    }
    public static List<String> GetListUpdateValue(String s)
    {
        List<String> kq=new ArrayList<String>();
        String[] temp=s.split(",");
        for (int i=0;i<temp.length;i++)
        {
            temp[i]=temp[i].trim();
            kq.add(temp[i]);
        }
        return kq;
    }
    public static boolean CheckUpdateValue()
    {
        for (int i=0;i<ListUpdateValue.size();i++)
            if (!CheckEqualValue(ListUpdateValue.get(i)))
                return false;
        return true;
    }
    public static boolean CheckEqualValue(String str)
    {
        String[] temp=str.split("=");
        if (temp.length!=2)
            return false;
        if (!IsRegular(temp[0])||!IsRegular(temp[1]))
            return false;
        return true;
    }
     public static List<ParseTable> GetParseTable()
    {
        List<ParseTable> listParseTable=new ArrayList<ParseTable>();
        for (int i=0;i<list_tableName.size();i++)
        {
            ParseTable pTable=new ParseTable();
            String str=list_tableName.get(i).trim();
            String[] temp=str.split(" ");
            if (temp.length==1)
            {
                pTable.TableName=temp[0];
                pTable.TableChar=temp[0];
            }
            else 
                if (temp.length==2)
                {
                    pTable.TableName=temp[0];
                    pTable.TableChar=temp[1];
                }
                else
                    return null;
            list_tableName.set(i, pTable.TableName);
           listParseTable.add(pTable);
        }
        List<ParseTable> listJoinParseTable=new ArrayList<ParseTable>();
        for (int i=0;i<getJoinTable().size();i++)
        {
            ParseTable pTable=new ParseTable();
            String str=getJoinTable().get(i).trim();
            String[] temp=str.split(" ");
            if (temp.length==1)
            {
                pTable.TableName=temp[0];
                pTable.TableChar=temp[0];
            }
            else 
                if (temp.length==2)
                {
                    pTable.TableName=temp[0];
                    pTable.TableChar=temp[1];
                }
                else
                    return null;
            getJoinTable().set(i, pTable.TableName);
           listJoinParseTable.add(pTable);
        }
        listParseTable.addAll(listJoinParseTable);
        return listParseTable;
    }
     public static List<ParseColumn> GetParseColumn(List<String> listColumName)
    {
        List<ParseColumn> listParseColumn=new ArrayList<ParseColumn>();
        for (int i=0;i<listColumName.size();i++)
        {
            ParseColumn pColumn=new ParseColumn();
            String str=listColumName.get(i).trim();
            String[] temp=str.split("\\.");
            if (temp.length==1)
            {
                pColumn.ColumnName=temp[0];
                pColumn.TableName=ListTable.get(0).TableName;
            }
            else 
                if (temp.length==2)
                {
                    if (GetParseTableName(temp[0])!="")
                    {
                        pColumn.ColumnName=temp[1];
                        pColumn.TableName=GetParseTableName(temp[0]);
                    }
                    else 
                        return null;
                }
                else
                    return null;
            listParseColumn.add(pColumn);
        }
        return listParseColumn;
    }
     public static String GetParseTableName(String t)
     {
         for (int i=0;i<ListTable.size();i++)
         {
             if (ListTable.get(i).TableChar.equals(t))
                 return ListTable.get(i).TableName;
         }
         return "";
     }
     public static List<Ve> DinhDangVe(List<String> veve)
     {
         List<Ve> kq=new ArrayList<Ve>();
         for (int i=0;i<veve.size();i++)
         {
             Ve ve=new Ve();
             String str=veve.get(i).trim();
             if (IsNumber(str)||(str.charAt(0)=='\''&&str.charAt(str.length()-1)=='\''))
             {
                 ve.setType(0);
                 ve.setValue(str);
             }
             else 
             {
                 ve.setType(1);
                 ve.setColumn(MakeParseColumn(str));
             }
             kq.add(ve);
         }
         return kq;
     }
     public static boolean IsNumber(String str)
     {
         for (int i=0;i<str.length();i++)
             if (str.charAt(i)<'0'||str.charAt(i)>'9')
                 return false;
         return true;
     }
     public static ParseColumn MakeParseColumn(String str)
     {
         ParseColumn pColumn=new ParseColumn();
         String[] temp=str.split("\\.");
            if (temp.length==1)
            {
                pColumn.ColumnName=temp[0];
                pColumn.TableName=ListTable.get(0).TableName;
            }
            else 
                if (temp.length==2)
                {
                    if (GetParseTableName(temp[0])!="")
                    {
                        pColumn.ColumnName=temp[1];
                        pColumn.TableName=GetParseTableName(temp[0]);
                    }
                    else 
                        return null;
                }
                else
                    return null;
         return pColumn;
     }
     public static void DinhDangDieuKien()
     {
         sWhereCondition.SetVeTrai(DinhDangVe(sWhereCondition.GetLeftString()));
         sWhereCondition.SetVePhai(DinhDangVe(sWhereCondition.GetLRightString()));
         sJoinCondition.SetVeTrai(DinhDangVe(sJoinCondition.GetLeftString()));
         sJoinCondition.SetVePhai(DinhDangVe(sJoinCondition.GetLRightString()));
     }
   /*  public String FormatString(String str)
     {
         String s=CurrentSQLString.toLowerCase();
     }*/
}
